package com.lizard.schedule.persistence.db.table;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.lizard.schedule.persistence.db.DbHelper;
import com.lizard.schedule.persistence.db.bean.Schedule;
import com.lizard.schedule.persistence.db.bean.ScheduleVersion;

/**
 * 用户的计划总版本号表
 */
public class ScheduleVersionTbl {

    private static ScheduleVersionTbl instance;

    /**
     * 表名
     */
    private final String TBL_NAME = "b_tbl";

    /**
     * 表字段：id
     */
    private final String FIELD_ID = "_id";

    /**
     * 表字段：用户名
     */
    private final String FIELD_USERNAME = "f_a";

    /**
     * 表字段：用户计划总版本号
     */
    private final String FIELD_SCHEDULE_VERSION = "f_b";

    private ScheduleVersionTbl(){}

    public static ScheduleVersionTbl getInstacne(){
        if(instance == null){
            synchronized (ScheduleVersionTbl.class){
                if(instance == null){
                    instance = new ScheduleVersionTbl();
                }
            }
        }
        return instance;
    }

    /**
     * 根据用户名查询计划版本号
     *
     * @param username
     * @return
     */
    public ScheduleVersion select(String username){
        ScheduleVersion scheduleVersion = null;

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ").append(TBL_NAME)
                .append(" WHERE ").append(FIELD_USERNAME).append(" = ?;");
        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(), new String[]{username});
        if(cursor.moveToNext()){
            scheduleVersion = new ScheduleVersion();
            scheduleVersion.setUsername(cursor.getString(cursor.getColumnIndex(FIELD_USERNAME)));
            scheduleVersion.setVersion(cursor.getInt(cursor.getColumnIndex(FIELD_SCHEDULE_VERSION)));
        }
        cursor.close();

        return scheduleVersion;
    }

    /**
     * 更新数据
     *
     * @param scheduleVersion
     */
    public void update(ScheduleVersion scheduleVersion){
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("UPDATE ").append(TBL_NAME)
                .append(" SET ").append(FIELD_SCHEDULE_VERSION).append(" = ? WHERE ")
                .append(FIELD_USERNAME).append(" = ?;");
        DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString(),
                new Object[]{scheduleVersion.getVersion(), scheduleVersion.getUsername()});
    }

    /**
     * 新增数据
     *
     * @param scheduleVersion
     */
    public void insert(ScheduleVersion scheduleVersion){
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("INSERT INTO ").append(TBL_NAME).append("(")
                .append(FIELD_USERNAME).append(",")
                .append(FIELD_SCHEDULE_VERSION).append(") VALUES(?, ?);");
        DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString(),
                new Object[]{scheduleVersion.getUsername(), scheduleVersion.getVersion()});
    }

    /**
     * 创建表
     *
     * @param db
     */
    public void createTbl(SQLiteDatabase db){
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("CREATE TABLE IF NOT EXISTS ").append(TBL_NAME).append("(")
                .append(FIELD_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,")
                .append(FIELD_USERNAME).append(" TEXT,")
                .append(FIELD_SCHEDULE_VERSION).append(" INTEGER")
                .append(");");
        db.execSQL(sqlBuilder.toString());
    }

    /**
     * 删除表
     *
     * @param db
     */
    public void dropTbl(SQLiteDatabase db){
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("DROP TABLE IF EXISTS ").append(TBL_NAME);
        db.execSQL(sqlBuilder.toString());
    }

}
